package dbbaas;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import baas.ColumnValue;
import baas.Row;
import baas.Table;
import baas.Util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class DBServlet extends HttpServlet {

	public DBServlet() {
		super();
	}

	@Override
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	@Override
	public void service(ServletRequest request, ServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		System.out.println("DBServlet-----------response");
		try {
			String action = request.getParameter("action");
			if ("query".equals(action)) {
				query(request, response);
			} else if ("execute".equals(action)) {
				execute(request, response);
			} else if ("getObject".equals(action)) {
				getObject(request, response);
			} else if ("putObject".equals(action)) {
				putObject(request, response);
			} else if ("deleteObject".equals(action)) {
				deleteObject(request, response);
			} else {
				super.service(request, response);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServletException(e);
		}
	}

	@Override
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		System.out.println("DBServlet-----------doget");
		doPost(request, response);
	}

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String appid = request.getParameter("appid");
		String appname = request.getParameter("appname");
		String note = request.getParameter("note");
		String appkey = MD5.GetMD5Code(appid);
		System.out.println("DBServlet-----------dopost");
		AppEntity appentity = new AppEntity();
		appentity.setAppid(appid);
		appentity.setAppname(appname);
		appentity.setNote(note);
		appentity.setAppkey(appkey);
		System.out.println(appentity);
		if (new DBUtil().isExist(appid)) {
			System.out.println("存在");
		} else {
			new DBUtil().addData(appid);
			new DBUtil().add(appentity);

		}
	}

	@Override
	public void init() throws ServletException {
		// Put your code here
	}

	private void query(ServletRequest request, ServletResponse response)
			throws SQLException, IOException, NamingException {
		SimpleDateFormat dfDateTime = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");// 设置日期格式
		SimpleDateFormat dfDate = new SimpleDateFormat("yyyy-MM-dd");// 设置日期格式
		SimpleDateFormat dfTime = new SimpleDateFormat("HH:mm:ss");// 设置日期格式
		SimpleDateFormat dfChinaDateTime = new SimpleDateFormat(
				"yyyy年MM月dd日 HH:mm:ss");// 设置日期格式
		SimpleDateFormat dfChinaDate = new SimpleDateFormat("yyyy年MM月dd日");// 设置日期格式
		SimpleDateFormat dfChinaTime = new SimpleDateFormat("HH:mm:ss");// 设置日期格式
		System.out.println("DBServilet-----Query-----"
				+ dfDateTime.format(new Date()));
		// 参数序列化
		// Parameter params = request.getParameter("params");
		// 定义外壳
		ResMsgItem resMsgItem = new ResMsgItem();
		resMsgItem.setSum(0);
		resMsgItem.setCode(0);
		String sTime = dfDateTime.format(new Date());
		// JSONArray jsonDataArray = new JSONArray();
		// resMsgItem.setData(jsonDataArray);
		resMsgItem.setTime(sTime);

		// 获取参数
		String appkey = request.getParameter("appkey"); // 列定义
		String sql = request.getParameter("sql");
		System.out.println("sql=" + sql);
		// 通过appkey查询apiid
		Connection conn = DBUtil.getDBConnection("cloudservice");// 获取数据库连接
		
		Connection connCloud = null;
		String sql1 = "select appid from appconfig where appkey=\'" + appkey
				+ "\'";
		PreparedStatement pstm1 = conn.prepareStatement(sql1);
		ResultSet rs1 = pstm1.executeQuery();
		if (rs1.next()) {// 判断对应appkey的数据库是否存在
			connCloud = DBUtil.getDBConnection(rs1.getString("appid"));// 获取对应appkey的数据库连接
		} else {
			resMsgItem.setCode(1);
			resMsgItem.setMessage("error");
			resMsgItem.setMessage("该APPKey无效！无法查找到数据！");
			Util.writeJsonToResponse(response, resMsgItem);// 在页面输出信息
		}
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
		// PreparedStatement pstm2 = conn.prepareStatement(sql);
		// ResultSet rs2 = pstm2.executeQuery();
		// 通过appid查询指向的数据库
		// 判断数据库是否存在
		// 通过指向的数据库得到Conn 数据库连接
		// 通过数据库连接执行SQL
		if (connCloud != null && !connCloud.isClosed()) {
			Table table = null;
			try {
				table = Util.queryData(connCloud, sql, null, null, null, null);
			} catch (SQLException e) {
				resMsgItem.setCode(1);
				resMsgItem.setMessage(e.getMessage());
				resMsgItem.setMsg("查询数据错误！");
				Util.writeJsonToResponse(response, resMsgItem);
			} finally {
				connCloud.close();
			}
			if (table != null) {
				List<Row> listRow = table.getRows();
				JSONArray jsonArray = new JSONArray();
				if (listRow != null && listRow.size() > 0) {
					for (int i = 0; i < listRow.size(); i++) {
						Row row = listRow.get(i);
						Map<String, ColumnValue> col = row.getValues();
						JSONObject json = new JSONObject();
						for (String key : col.keySet()) {
							Object o = row.getValue(key);
							if (o != null) {
								if (o instanceof String) { // 字符串类型
									json.put(key, row.getString(key));
								} else if (o instanceof Integer) { // 整型
									json.put(key, row.getInteger(key));
								} else if (o instanceof Float) { // 浮点型
									json.put(key, row.getFloat(key));
								} else if (o instanceof Boolean) { // 布尔型
									json.put(key, row.getBoolean(key));
								} else if (o instanceof BigDecimal) { // 精度
									json.put(key, row.getDecimal(key));
								} else if (o instanceof java.sql.Date) { // 日期
									String sDate = dfDate.format(row
											.getDate(key));
									String sDateChina = dfChinaDate.format(row
											.getDate(key));
									json.put(key, sDate);
									json.put(key + "_china", sDateChina);
									// json.put(key, row.getDate(key));
								} else if (o instanceof java.sql.Time) { // 时间
									String sTimeTemp = dfTime.format(row
											.getTime(key));
									String sChinaTimeTemp = dfChinaTime
											.format(row.getTime(key));
									json.put(key, sTimeTemp);
									json.put(key + "_china", sChinaTimeTemp);
									// json.put(key, row.getTime(key));
								} else if (o instanceof java.sql.Timestamp) { // 时间戳类型
									String sDateTime = dfDateTime.format(row
											.getDateTime(key));
									String sChinaDateTime = dfChinaDateTime
											.format(row.getDateTime(key));
									json.put(key, sDateTime);
									json.put(key + "_china", sChinaDateTime);
								} else {
									json.put(key, o); // 其他类型
								}
							} else {
								json.put(key, null); // 值为null时
							}
						}
						jsonArray.add(json);
					}
				} else {

				}
				if (resMsgItem.getCode() == 0) {
					resMsgItem.setCode(0);
					resMsgItem.setStatus(resMsgItem.getCode());
					resMsgItem.setMessage("success");
					resMsgItem.setMsg("查询数据完成！");
					resMsgItem.setSum(jsonArray.size());
					resMsgItem.setData(jsonArray);
					// 输出返回结果 JSON
					// JSONObject json = Transform.tableToJson(table);
					// Util.writeJsonToResponse(response, json);
					Util.writeJsonToResponse(response, resMsgItem);
				}
			}
		}

	}

	private void execute(ServletRequest request, ServletResponse response)
			throws SQLException, IOException, NamingException {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		System.out.println("DBServilet-----execute-----"
				+ df.format(new Date()));

		// 参数序列化
		// Parameter params = request.getParameter("params");
		// 定义外壳
		ResMsgItem resMsgItem = new ResMsgItem();
		resMsgItem.setCode(0);
		resMsgItem.setSum(0);
		Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sTime = format.format(new Date());
		// JSONArray jsonDataArray = new JSONArray();
		// resMsgItem.setData(jsonDataArray.toString());
		resMsgItem.setTime(sTime);

		// 获取参数
		String appkey = request.getParameter("appkey"); // 列定义
		String sql = request.getParameter("sql");
		System.out.println("sql=" + sql);
		// 通过appkey查询apiid
		Connection conn = DBUtil.getDBConnection("cloudservice");
		Connection connCloud = null;
		String sql1 = "select appid from appconfig where appkey=\'" + appkey
				+ "\'";
		PreparedStatement pstm1 = conn.prepareStatement(sql1);
		ResultSet rs1 = pstm1.executeQuery();
		if (rs1.next()) {
			connCloud = DBUtil.getDBConnection(rs1.getString("appid"));
		} else {
			resMsgItem.setCode(1);
			resMsgItem.setStatus(1);
			resMsgItem.setMessage("error");
			Util.writeJsonToResponse(response, resMsgItem);
		}
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
		
		// PreparedStatement pstm2 = conn.prepareStatement(sql);
		// ResultSet rs2 = pstm2.executeQuery();
		// 通过appid查询指向的数据库
		// 判断数据库是否存在
		// 通过指向的数据库得到Conn 数据库连接

		// 通过数据库连接执行SQL
		if (connCloud != null && !connCloud.isClosed()) {
			PreparedStatement newStat = connCloud.prepareStatement(sql);
			try {
				newStat.execute();
			} catch (SQLException e) {
				resMsgItem.setCode(1);
				resMsgItem.setMessage(e.getMessage());
				resMsgItem.setMsg("执行脚本错误！");
				Util.writeJsonToResponse(response, resMsgItem);
			} finally {
				newStat.close();
				connCloud.close();
			}
			if (resMsgItem.getCode() == 0) {
				resMsgItem.setCode(0);
				resMsgItem.setMessage("success");
				resMsgItem.setMsg("执行脚本完成！");
				// 输出返回结果 JSON
				// JSONObject json = Transform.tableToJson(table);
				// Util.writeJsonToResponse(response, json);
				Util.writeJsonToResponse(response, resMsgItem);
			}
		}
	}

	private void getObject(ServletRequest request, ServletResponse response)
			throws SQLException, IOException, NamingException {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		System.out.println("DBServilet-----getObject-----"
				+ df.format(new Date()));
		// 参数序列化
		// Parameter params = request.getParameter("params");
		// 定义外壳
		ResMsgItem resMsgItem = new ResMsgItem();
		resMsgItem.setSum(0);
		Format format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String sTime = format.format(new Date());
		JSONArray jsonDataArray = new JSONArray();
		resMsgItem.setData(jsonDataArray);
		resMsgItem.setTime(sTime);
		// 获取参数
		String cloudkey = request.getParameter("cloudkey");
		// 通过appkey查询apiid
		String appkey = request.getParameter("appkey"); // 列定义
		// 通过appkey查询apiid
		Connection conn = DBUtil.getDBConnection("cloudservice");// 获取数据库连接
		Connection connCloud = null;
		String sql1 = "select appid from appconfig where appkey=\'" + appkey
				+ "\'";
		String sql = "select value from cloudobject where cloudkey=\'"
				+ cloudkey + "\'";
		PreparedStatement pstm1 = conn.prepareStatement(sql1);
		ResultSet rs1 = pstm1.executeQuery();
		if (rs1.next()) {// 判断对应appkey的数据库是否存在
			connCloud = DBUtil.getDBConnection(rs1.getString("appid"));// 获取对应appkey的数据库连接
		} else {
			resMsgItem.setCode(1);
			resMsgItem.setStatus(1);
			resMsgItem.setMessage("error");
			Util.writeJsonToResponse(response, resMsgItem);// 在页面输出信息
		}
		if (conn != null) {
			conn.close();
		}
		if (pstm1 != null) {
			pstm1.close();
		}

		// 通过appid查询指向的数据库
		// 判断数据库是否存在
		// 通过指向的数据库得到Conn 数据库连接
		// 通过数据库连接执行SQL
		if (connCloud != null && !connCloud.isClosed()) {
			Table table = null;
			try {
				table = Util.queryData(connCloud, sql, null, null, null, null);
			} catch (SQLException e) {
				resMsgItem.setCode(1);
				resMsgItem.setMessage(e.getMessage());
				resMsgItem.setMsg("查询数据错误！");
				Util.writeJsonToResponse(response, resMsgItem);
			} finally {
				connCloud.close();
			}
			if (table != null) {
				List<Row> listRow = table.getRows();
				if (listRow != null && listRow.size() > 0) {
					Row row = listRow.get(0);
					// Map<String, ColumnValue> col = row.getValues();
					// JSONObject json = new JSONObject();
					String sData = row.getString("value");
					JSONObject json = new JSONObject();
					try {
						json = JSON.parseObject(sData);
						resMsgItem.setCode(0);
						resMsgItem.setMessage("success");
						resMsgItem.setMsg("查询数据完成！");
					} catch (Exception e) {
						resMsgItem.setCode(3);
						resMsgItem.setMessage("error");
						resMsgItem.setMsg("值不是一个标准的JSON！");
					}
					resMsgItem.setValue(json);
					// json.put(value, row.getString(cloudkey));
				} else {
					resMsgItem.setCode(2);
					resMsgItem.setMessage("error");
					resMsgItem.setMsg("没有查找到该Key的对象！");
				}
				// resMsgItem.setSum(jsonArray.size());
				// resMsgItem.setData(jsonArray);
				// 输出返回结果 JSON
				// JSONObject json = Transform.tableToJson(table);
				// Util.writeJsonToResponse(response, json);
				Util.writeJsonToResponse(response, resMsgItem);
			}
		}

	}

	private void putObject(ServletRequest request, ServletResponse response)
			throws SQLException, IOException, NamingException {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		System.out.println("DBServilet-----putObject-----"
				+ df.format(new Date()));
		// 参数序列化
		// Parameter params = request.getParameter("params");
		// 定义外壳
		ResMsgItem resMsgItem = new ResMsgItem();
		resMsgItem.setSum(0);
		Format format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String sTime = format.format(new Date());
		// JSONArray jsonDataArray = new JSONArray();
		// resMsgItem.setData(jsonDataArray.toString());
		resMsgItem.setTime(sTime);
		// 获取参数
		String appkey = request.getParameter("appkey"); // 列定义
		String cloudkey = request.getParameter("cloudkey");
		String value = request.getParameter("value");
		String note = request.getParameter("note");
		String sql = null;
		String sqlInsert = "insert into cloudobject (cloudkey,value,note) value (\""
				+ cloudkey + "\",\"" + value + "\",\"" + note + "\")";
		String sqlUpdate = "update  cloudobject set value=\"" + value + "\""
				+ "," + "note=\"" + note + "\"" + " where cloudkey =\""
				+ cloudkey + "\"";
		// 通过appkey查询appid
		Connection conn = DBUtil.getDBConnection("cloudservice");
		Connection connCloud = null;
		String sql1 = "select appid from appconfig where appkey=\'" + appkey
				+ "\'";
		String sql2 = "select cloudkey from cloudobject where cloudkey=\'"
				+ cloudkey + "\'";
		PreparedStatement pstm1 = conn.prepareStatement(sql1);
		ResultSet rs1 = pstm1.executeQuery();
		if (rs1.next()) {
			connCloud = DBUtil.getDBConnection(rs1.getString("appid"));
		} else {

		}
		if (conn != null) {
			conn.close();
			// resMsgItem.setCode(1);
			// resMsgItem.setStatus(1);
			// resMsgItem.setMessage("error");
			// Util.writeJsonToResponse(response, resMsgItem);
		}
		if (pstm1 != null) {
			pstm1.close();
		}
		PreparedStatement pstm2 = connCloud.prepareStatement(sql2);
		ResultSet rs2 = pstm2.executeQuery();
		// PreparedStatement pstm2 = conn.prepareStatement(sql);
		// ResultSet rs2 = pstm2.executeQuery();
		// 通过appid查询指向的数据库
		// 判断数据库是否存在
		// 通过指向的数据库得到Conn 数据库连接
		// 通过数据库连接执行SQL
		if (connCloud != null && !connCloud.isClosed()) {
			if (rs2.next()) {
				sql = sqlUpdate;
			} else {
				sql = sqlInsert;
			}
			PreparedStatement newStat = connCloud.prepareStatement(sql);
			try {
				newStat.execute();
			} catch (SQLException e) {
				resMsgItem.setCode(1);
				resMsgItem.setMessage(e.getMessage());
				resMsgItem.setMsg("执行脚本错误！");
				Util.writeJsonToResponse(response, resMsgItem);
			} finally {
				newStat.close();
				connCloud.close();
				if (pstm2 != null) {
					pstm2.close();
				}
			}
			resMsgItem.setCode(0);
			resMsgItem.setMessage("success");
			resMsgItem.setMsg("执行脚本完成！");
			// 输出返回结果 JSON
			// JSONObject json = Transform.tableToJson(table);
			// Util.writeJsonToResponse(response, json);
			Util.writeJsonToResponse(response, resMsgItem);
		}
	}

	private void deleteObject(ServletRequest request, ServletResponse response)
			throws SQLException, IOException, NamingException {
		System.out.println("DemoServlet------------------putObject");
		// 参数序列化
		// Parameter params = request.getParameter("params");
		// 定义外壳
		ResMsgItem resMsgItem = new ResMsgItem();
		resMsgItem.setSum(0);
		Format format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String sTime = format.format(new Date());
		// JSONArray jsonDataArray = new JSONArray();
		// resMsgItem.setData(jsonDataArray.toString());
		resMsgItem.setTime(sTime);
		// 获取参数
		String appkey = request.getParameter("appkey"); // 列定义
		String cloudkey = request.getParameter("cloudkey");
		String sqlDelete = "delete from cloudobject where cloudkey =\""
				+ cloudkey + "\"";
		// 通过appkey查询appid
		Connection conn = DBUtil.getDBConnection("cloudservice");
		Connection connCloud = null;
		String sql1 = "select appid from appconfig where appkey=\'" + appkey
				+ "\'";
		PreparedStatement pstm1 = conn.prepareStatement(sql1);
		ResultSet rs1 = pstm1.executeQuery();
		if (rs1.next()) {
			connCloud = DBUtil.getDBConnection(rs1.getString("appid"));
		} else {

		}
		if (conn != null) {
			conn.close();
		}
		if (pstm1 != null) {
			pstm1.close();
		}

		if (connCloud != null && !connCloud.isClosed()) {
			PreparedStatement newStat = connCloud.prepareStatement(sqlDelete);
			try {
				if (!newStat.execute()) {
					resMsgItem.setCode(1);
					resMsgItem.setMsg("数据不存在！");
					Util.writeJsonToResponse(response, resMsgItem);
					return;
				}
			} catch (SQLException e) {
				resMsgItem.setCode(1);
				resMsgItem.setMessage(e.getMessage());
				resMsgItem.setMsg("执行脚本错误！");
				Util.writeJsonToResponse(response, resMsgItem);
			} finally {
				newStat.close();
				connCloud.close();
			}
			resMsgItem.setCode(0);
			resMsgItem.setMessage("success");
			resMsgItem.setMsg("执行脚本完成！");
			Util.writeJsonToResponse(response, resMsgItem);
		}
	}
}
